﻿using System;
using System.Data;
using System.Data.SQLite;
namespace MyFunction.SQLiteHelper
{
	/// <summary>
	/// Sqlite 数据处理类
	/// </summary>
	public class SqliteHelper
	{
		
		 // Declartion internal variables
        /// <summary>
        ///  sqlite 联接对象
        /// </summary>
        private SQLiteConnection m_connection = null;
        /// <summary>
        ///  联接字串，在函数初始化时赋值
        /// </summary>
        private string m_connectionString = "";
        /// <summary>
        ///  m_dataAdapter
        /// </summary>
        private SQLiteDataAdapter m_dataAdapter = null;
        /// <summary>
        ///  m_dataSet 查询结果集
        /// </summary>
        private DataSet m_dataSet = null;
        /// <summary>
        /// m_fieldNameID
        /// </summary>
        private string m_fieldNameID = "";

        // The DataSet is filled with the methode LoadDataSet
        /// <summary>
        ///  属性：取查询结果集
        /// </summary>
        public DataSet DataSet
        {
            get { return m_dataSet; }
        }

        // Constructor -> ConnectionString is required
        /// <summary>
        ///  构造函数，给联接字串初始化
        /// </summary>
        /// <param name="connectionString">联接字符串</param>
        public SqliteHelper(string connectionString)
        {
            m_connectionString = connectionString;
        }

        // Load the DataSet 
        /// <summary>
        ///  执行SQL 查询语句，生成DataSet
        ///  如果有关键字，则生成 update ,delete ,insert 相关指令
        ///  数据集通过 DataSet 方法获取
        /// </summary>
        /// <param name="commandText">Sql 语句</param>
        /// <param name="fieldNameID">关键字</param>
        /// <returns>false ,true </returns>
        public bool Load(string commandText, string fieldNameID)
        {
            // Save the variables
            m_fieldNameID = fieldNameID;

            try
            {
                // 打开联接
                m_connection = new SQLiteConnection(m_connectionString);
                m_connection.Open();

                //建立 DataAdapter
                m_dataAdapter = new SQLiteDataAdapter(commandText, m_connection);

                // Koppel een eventhandler aan het RowUpdated-event van de DataAdapter
                //m_dataAdapter.RowUpdated += new SqlRowUpdatedEventHandler(m_dataAdapter_RowUpdated);
                //m_dataAdapter.RowUpdated += m_dataAdapter_RowUpdated;
                m_dataSet = new DataSet();

                // Voor eventueel opslaan --> Commands maken
                //如果关键字不空，则生成插入、更新、删除指令
                if (!string.IsNullOrEmpty(fieldNameID))
                {
                    SQLiteCommandBuilder commandBuilder = new SQLiteCommandBuilder(m_dataAdapter);
                    m_dataAdapter.InsertCommand = commandBuilder.GetInsertCommand();
                    m_dataAdapter.DeleteCommand = commandBuilder.GetDeleteCommand();
                    m_dataAdapter.UpdateCommand = commandBuilder.GetUpdateCommand();
                }

                // 填充dataSet
                m_dataAdapter.Fill(m_dataSet);

                // 返回值
                return true;
            }

            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                // Altijd netjes sluiten
                m_connection.Close();
            }
        }


        // Load the DataSet ,多态
        /// <summary>
        ///    执行SQL 查询语句，生成DataSet
        /// </summary>
        /// <param name="commandText">sql 语句</param>
        /// <returns>true ,false</returns>
        public bool Load(string commandText)
        {
            return Load(commandText, "");
        }
        //------------------------------------

        #region 保存数据，将m_dataSet保存到数据
        /// <summary>
        ///  方法：保存数据，将m_dataSet保存到数据
        ///  在调用Load 方法，要设定关键字，才能保存数据
        /// </summary>
        /// <returns>true ,false </returns>
        public bool Save()
        {
            //如查关键字为空，则直接返回
            if (m_fieldNameID.Trim().Length == 0)
            {
                return false;
            }

            try
            {
                // Open de connectie
                m_connection.Open();

                //调用 dataadapter 更新方法，更新 dataset
                m_dataAdapter.Update(m_dataSet);

                // We zijn hier, alles okay!
                return true;
            }

            catch (Exception)
            {
                throw;
            }
            finally
            {
                // Altijd netjes sluiten
                m_connection.Close();
            }
        }
        #endregion


    }
		
}
